﻿-- =============================================
-- Author:		<Евгений Веприков>
-- Create date: <13,07,2006>
-- Description:	<Поиск товара по наименованию, штрихкоду партии, номеру документа>
-- =============================================
CREATE PROCEDURE [dbo].[GoodsSearch] 
@DocId int,
@Barcode varchar(12), --было varchar(13) Черноусов 13.08.06
@Name varchar(150)
AS
BEGIN
IF @DocId IS NOT NULL 
/* Возврат - поиск в расходном документе*/
IF @Barcode IS NOT NULL 
/* По штрих коду*/ 
SELECT ReturnLineId = e.Id, PartyId = p.Id,  Barcode = p.barcode, Name = v.FullName,  
ManufacturerName = v.ManufacturerName,  RetailPrice = p.RetailPrice, 
Qntty = - e.Qntty - sum(COALESCE (i.Qntty, 0))
FROM  ca.GoodsDocLines e /* расход*/ INNER JOIN  cd.Parties p 
ON p.Id = e.PartyId AND p.barcode = @Barcode 
JOIN  dbo.vwGoodsDetailed v 
ON v.Id = p.GoodsId 
INNER JOIN ca.Goods g ON g.Id = p.GoodsId 
LEFT JOIN ca.GoodsDocLines i /* возврат (приход), если есть*/ 
ON i.ReturnLineId = e.Id WHERE   e.DocId = @DocId 
GROUP BY e.Id, p.Id, p.barcode, v.FullName, v.ManufacturerName, p.RetailPrice, e.Qntty 
ORDER BY v.FullName,  v.ManufacturerName 
ELSE 
/* По наименованию*/ 
SELECT ReturnLineId = e.Id, PartyId = p.Id, Barcode = p.barcode, 
Name = v.FullName, ManufacturerName = v.ManufacturerName,RetailPrice = p.RetailPrice, 
Qntty = - e.Qntty - sum(COALESCE (i.Qntty,0))
FROM  ca.GoodsDocLines e /* расход*/INNER JOIN cd.Parties p 
ON p.Id = e.PartyId INNER JOIN ca.Goods g 
ON g.Id = p.GoodsId INNER JOIN dbo.vwGoodsDetailed v 
ON v.Id = p.GoodsId AND v.FullName LIKE @Name 
LEFT OUTER JOIN ca.GoodsDocLines i /* возврат (приход), если есть*/ 
ON i.ReturnLineId = e.Id 
WHERE e.DocId = @DocId 
GROUP BY e.Id, p.Id, p.barcode, v.FullName, v.ManufacturerName, p.RetailPrice, e.Qntty 
ORDER BY v.FullName, v.ManufacturerName 
ELSE /* Отпуск товара - поиск в справочниках*/ 
IF @Barcode IS NOT NULL
/* По штрих коду*/ 
SELECT ReturnLineId = CONVERT(int, NULL), PartyId = p.Id, Barcode = p.barcode, 
Name = v.FullName, ManufacturerName = v.ManufacturerName,RetailPrice = p.RetailPrice,
Qntty = p.RestQntty
FROM cd.Parties p JOIN ca.Goods g ON g.Id = p.GoodsId INNER JOIN
dbo.vwGoodsDetailed v ON v.Id = p.GoodsId
WHERE     p.barcode = @Barcode AND  p.blocked = 0
ORDER BY v.FullName, v.ManufacturerName 
ELSE/* По наименованию*/ 
SELECT ReturnLineId = CONVERT(int,NULL), PartyId = p.Id,Barcode = p.barcode,
Name = v.FullName, ManufacturerName = v.ManufacturerName,RetailPrice = p.RetailPrice,
Qntty = p.RestQntty
FROM  ca.Assortment a INNER JOIN ca.Goods g 
ON g.AssortmentId = a.Id INNER JOIN dbo.vwGoodsDetailed v 
ON v.Id = g.Id INNER JOIN cd.Parties p 
ON p.GoodsId = g.Id AND p.blocked = 0 AND p.RestQntty > 0
WHERE     a.Name LIKE @Name
ORDER BY v.FullName, v.ManufacturerName
END

